//201902104011 王雪梦
package bysj.dao;
import bysj.domain.GraduateProjectType;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class GraduateProjectTypeDao {
    private static GraduateProjectTypeDao graduateProjectTypeDao =null;
    //将构造器私有，防止本类外的类生成本类的对象
    private GraduateProjectTypeDao() {
    }
    //返回本类唯一的对象
    public static GraduateProjectTypeDao getInstance() {
        // 静态变量graduateProjectTypeDao为空时，才创建SchoolDao对象。
        // 第二次调用本方法graduateProjectTypeDao就不空了，不再创建对象。
        if( graduateProjectTypeDao==null){
            graduateProjectTypeDao = new GraduateProjectTypeDao();
        }
        return graduateProjectTypeDao;
    }

    public Collection<GraduateProjectType> finaAll(Connection connection, List<Condition> conditionList, Pagination pagination)throws SQLException  {
        ///创建集合类对象，用来保存所有记录代表的Department对象
        Collection<GraduateProjectType> graduateProjectTypes = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM graduateProjectType");
        int totalNum = CommonDao.getInstance().count(connection,"graduateProjectType");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            GraduateProjectType graduateProjectType = null;
            int id= resultSet.getInt(1);
            //为graduateProjectType对象的description赋值
            String description = resultSet.getString(2);
            //为graduateProjectType对象的no赋值
            String no = resultSet.getString(3);
            //为graduateProjectType对象的remarks赋值
            String remarks = resultSet.getString(4);
            //将graduateProjectType增加到graduateProjectTypes集合
            graduateProjectType = new GraduateProjectType(id,description,no,remarks);
            graduateProjectTypes.add(graduateProjectType);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return graduateProjectTypes;
    }

    public GraduateProjectType find(Integer id,Connection connection) throws SQLException {
        //创建GraduateProjectType对象
        GraduateProjectType graduateProjectType = new GraduateProjectType(id,"description", "no", "remarks");
        //创建sql语句
        String graduateProjectType_sql = "SELECT * FROM ProjectType WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(graduateProjectType_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()){
            //为graduateProjectType对象的description赋值
            graduateProjectType.setDescription(resultSet.getString(2));
            //为graduateProjectType对象的no赋值
            graduateProjectType.setNo(resultSet.getString(3));
            //为graduateProjectType对象的remarks赋值
            graduateProjectType.setRemarks(resultSet.getString(4));
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return graduateProjectType;
    }

    public boolean update(GraduateProjectType graduateProjectType,Connection connection) throws SQLException {
        //创建sql语句
        String graduateProjectType_sql = "update graduateProjectType set no=?,description=?,remarks=? where id="+graduateProjectType.getId();
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(graduateProjectType_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,graduateProjectType.getNo());
        preparedStatement.setString(2,graduateProjectType.getDescription());
        preparedStatement.setString(3,graduateProjectType.getRemarks());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        //如果修改成功返回true，失败返回false
        if (affectedRowNum == 0) {
            return false;
        } else {
            return true;
        }
    }


    public Integer add(GraduateProjectType graduateProjectType,Connection connection) throws SQLException {
        //创建sql语句
        String graduateProjectType_sql = "INSERT INTO graduateProjectType(description,no,remarks) Values"+"(?,?,?)";
        System.out.println(graduateProjectType.getDescription());
        //获得id
        int id = graduateProjectType.getId();
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(graduateProjectType_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,graduateProjectType.getDescription());
        preparedStatement.setString(2,graduateProjectType.getNo());
        preparedStatement.setString(3,graduateProjectType.getRemarks());
        //执行预编译对象的executeUpdate方法，获得添加的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        //如果添加失败 返回null，添加成功，返回school对象的id
        if(affectedRowNum==0){
            return null;
        }else {
            return id;
        }
    }

    public boolean delete(Integer id, Connection connection) throws SQLException {
        //创建sql语句
        String school_sql = "delete from graduateProjectType where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(school_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        //isDelete的初始值为true
        boolean isDelete = true;
        //如果删除的记录的行数为0，那么isDelete为false
        if (affectedRowNum == 0) {
            isDelete = false;
        }
        return isDelete;
    }
}
